package rit.edu.cjp5465.simpGrocList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter
{
	private static final String TAG = "DBAdapter";
	private static final String DATABASE_NAME = "grocierLists";
	
	// Database Tables
	private static final String DBT_Shopping_Item = "shoppingItem";
	private static final String DBT_Shopping_List = "shoppingList";
	private static final String DBT_Item_Location = "itemLocation";
	private static final String DBT_Location_Type = "locationType";
	
	// Common Table Columns
	public static final String CL_ROWID = "_id";
	public static final String CL_NAME = "name";
	
	// Shopping Items Columns
	public static final String CL_SI_QUANTITY = "quantity";
	public static final String CL_SI_LOCATION = "location";
	public static final String CL_SI_NOTES = "notes";
	public static final String CL_SI_LIST = "list";
	
	// Shopping List Columns
	public static final String CL_SL_DATE = "date";
	
	// Location Columns
	public static final String CL_L_TYPE = "type";
	
	private static final int DATABASE_VERSION = 1;
	private static final String[] DATABASE_CREATE = {
		"CREATE TABLE type(" + 
		"	_id    INTEGER PRIMARY KEY, " + 
		"	name  TEXT" + 
		");" ,
		"CREATE TABLE location(" + 
		"	_id     INTEGER PRIMARY KEY, " + 
		"	name   TEXT, " + 
		"	type INTEGER," + 
		"	FOREIGN KEY(type) REFERENCES type(_id)" + 
		");" ,
		"CREATE TABLE shoppingList(" + 
		"	_id     INTEGER PRIMARY KEY, " + 
		"	name TEXT," + 
		"	date   DATE" + 
		");" , 
		"CREATE TABLE shoppingItem(" + 
		"	_id     INTEGER PRIMARY KEY, " + 
		"	name   TEXT, " + 
		"	notes TEXT," + 
		"	quantity INTEGER," + 
		"	location INTEGER," + 
		"	list INTEGER," + 
		"	FOREIGN KEY(location) REFERENCES location(_id)," + 
		"	FOREIGN KEY(list) REFERENCES shoppingList(_id)" + 
		");"
	};
	
	private final Context context;
	private DatabaseHelper DBHelper;
	private SQLiteDatabase db;
	
	public DBAdapter(Context ctx)
	{
		this.context = ctx;
		DBHelper = new DatabaseHelper(context);
	}
	private static class DatabaseHelper extends SQLiteOpenHelper
	{
		DatabaseHelper(Context context)
		{
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}
		@Override
		public void onCreate(SQLiteDatabase db)
		{
			for(String tbl_creation : DATABASE_CREATE)
			db.execSQL(tbl_creation);
		}
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion,
				int newVersion)
		{
			Log.w(TAG, "Upgrading database from version " + oldVersion
					+ " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS titles");
			onCreate(db);
		}
	}
	//---opens the database---
	public DBAdapter open() throws SQLException
	{
		db = DBHelper.getWritableDatabase();
		return this;
	}
	//---closes the database---
	public void close()
	{
		DBHelper.close();
	}
	//---insert a title into the database---
	public long insertItem(String name, int quantity, int location, String notes, int list)
	{
		ContentValues initialValues = new ContentValues();
		initialValues.put(CL_NAME, name);
		initialValues.put(CL_SI_QUANTITY, quantity);
		initialValues.put(CL_SI_LOCATION, location);
		initialValues.put(CL_SI_NOTES, notes);
		initialValues.put(CL_SI_LIST, list);
		return db.insert(DBT_Shopping_Item, null, initialValues);
	}
	
	public long insertList(String name, String date) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(CL_NAME, name);
		initialValues.put(CL_SL_DATE, date);
		return db.insert(DBT_Shopping_List, null, initialValues);
	}
	
	//---deletes a particular title---
	public boolean deleteItem(long rowId)
	{
		return db.delete(DBT_Shopping_Item, CL_ROWID +
				"=" + rowId, null) > 0;
	}
	
	public boolean deleteList(long rowId) {
		return db.delete(DBT_Shopping_List, CL_ROWID +
				"=" + rowId, null) > 0;		
	}
	//---retrieves all the Lists---
	public Cursor getAllLists()
	{
		return db.query(DBT_Shopping_List, new String[] {
				CL_ROWID,
				CL_NAME,
				CL_SL_DATE},
				null,
				null,
				null,
				null,
				CL_SL_DATE);
	}
	
	public Cursor getAllItems(Long listID)
	{
		return db.query(DBT_Shopping_Item, new String[] {
				CL_ROWID,
				CL_NAME,
				CL_SI_QUANTITY,
				CL_SI_LOCATION,
				CL_SI_NOTES,
				CL_SI_LIST},
				CL_SI_LIST + "=?",
				new String[] {listID.toString()},
				null,
				null,
				null);
	}
	//---retrieves a particular title---
	public Cursor getTitle(long rowId) throws SQLException
	{
		Cursor mCursor =
				db.query(true, DBT_Shopping_Item, new String[] {
						CL_ROWID,
						CL_SI_QUANTITY,
						CL_SI_LOCATION,
						CL_SI_NOTES,
						CL_SI_LIST
				},
				CL_ROWID + "=" + rowId,
				null,
				null,
				null,
				null,
				null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
}